CREATE procedure [dbo].[sp_asi_AdvancedFlowdown]
as
set nocount on
declare
@Short varchar(8000),
@Str varchar(255),
@Pos int,
@Len int,
@Cmd varchar(8000),
@TableName varchar(30),
@FieldName varchar(30)
update Name set Name.MEMBER_RECORD = Member_Types.MEMBER_RECORD
from Name, Member_Types
where Name.MEMBER_TYPE = Member_Types.MEMBER_TYPE
and Name.MEMBER_RECORD <> Member_Types.MEMBER_RECORD
update Name set Name.COMPANY_RECORD = Member_Types.COMPANY_RECORD
from Name, Member_Types
where Name.MEMBER_TYPE=Member_Types.MEMBER_TYPE
and Name.COMPANY_RECORD <> Member_Types.COMPANY_RECORD
create table
#TableNames (TABLE_NAME varchar(30) not null default '')
create table
#TablesAndFields (TABLE_NAME varchar(30) not null default 'Name',
FIELD_NAME varchar(30) not null default '')
select @Short = ShortValue from System_Params where ParameterName = 'Member_Control.FlowDownFields'
select @Len = datalength(@Short)
if @Len=0
BEGIN
select @Short = LongValue from System_Params where ParameterName = 'Member_Control.FlowDownFields'
END
select @Pos = charindex(',',@Short)
while @Pos > 0
BEGIN
select @Str = substring(@Short,1,@Pos - 1)
select @Short = substring(@Short,@Pos + 1,datalength(@Short) - @Pos)
select @Pos = charindex('.',@Str)
if @Pos > 0
BEGIN
insert #TablesAndFields
select substring(@Str,1,@Pos-1),substring(@Str,@Pos+1,datalength(@Str)-@Pos)
END
else
BEGIN
insert #TablesAndFields(FIELD_NAME)
select @Str
END
select @Pos = charindex(',',@Short)
END
select @Pos = charindex('.',@Short)
if @Pos > 0
BEGIN
insert #TablesAndFields
select substring(@Short,1,@Pos - 1),substring(@Short,@Pos + 1,datalength(@Short) - @Pos)
END
else
BEGIN
insert #TablesAndFields(FIELD_NAME)
select @Short
END
insert #TableNames
select distinct TABLE_NAME
from #TablesAndFields
declare GetTables cursor for
select TABLE_NAME
from #TableNames
open GetTables
fetch next from GetTables into @TableName
while @@fetch_status = 0
BEGIN
select @Cmd = 'update ' + @TableName + ' set '
declare GetFields cursor for
select FIELD_NAME
from #TablesAndFields
where TABLE_NAME = @TableName
open GetFields
fetch next from GetFields into @FieldName
while @@fetch_status = 0
BEGIN
select @Cmd = @Cmd + @FieldName + '=xxx.' + @FieldName + ','
fetch next from GetFields into @FieldName
END
close GetFields
deallocate GetFields
select @Cmd = substring(@Cmd,1,datalength(@Cmd)-1)
if @TableName = 'Name'
BEGIN
select @Cmd = @Cmd + ' from ' + @TableName + ' ' + @TableName + ',' + @TableName + ' xxx'
select @Cmd = @Cmd + ' where Name.CO_ID = xxx.ID and Name.COMPANY_RECORD=0'
END
else
BEGIN
select @Cmd = @Cmd + ' from ' + @TableName + ' ' + @TableName + ',' + @TableName + ' xxx,Name Name,Name n2'
select @Cmd = @Cmd + ' where Name.CO_ID = n2.ID and Name.ID = ' + @TableName + '.ID and n2.ID = xxx.ID'
select @Cmd = @Cmd + ' and Name.COMPANY_RECORD=0'
END
set nocount off
exec(@Cmd)
set nocount on
fetch next from GetTables into @TableName
END
close GetTables
deallocate GetTables

GO
GRANT EXECUTE ON  [dbo].[sp_asi_AdvancedFlowdown] TO [IMIS]
GO